package info.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

//import org.apache.jasper.tagplugins.jstl.core.Choose;

import info.bean.Ac01;
import info.bean.JiaTiao;
import info.data.DBHelper;

public class JiaTiaoDao
	{
		DBHelper dbHelper = new DBHelper();

		// ���������Ϣ
		public int insertJiaTiao(Date b_date, Date e_date, int days, int level,
				String reason, int xs_id) throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "insert into jiatiao "
						+ " values(null,now(),?,?,?,?,?,?,?,?,?,?,?,?)";
				PreparedStatement pstmt = null;
				try
					{
						pstmt = conn.prepareStatement(sql);
						pstmt.setDate(1, b_date);
						pstmt.setDate(2, e_date);
						pstmt.setInt(3, days);
						pstmt.setInt(4, level);
						pstmt.setString(5, reason);
						pstmt.setInt(6, 0);
						pstmt.setInt(7, 0);
						pstmt.setInt(8, 0);
						pstmt.setInt(9, 0);
						pstmt.setInt(10, 0);
						pstmt.setInt(11, 0);
						pstmt.setInt(12, xs_id);
						int result = pstmt.executeUpdate();
						return result;
					}
				catch (SQLException e)
					{
						e.printStackTrace();
					}
				finally
					{
						pstmt.close();
						conn.close();
					}
				return 0;
			}

		// �������˵�id��ѯ����
		public ArrayList findJiaTiaoByXs_id(int xs_id) throws NamingException,
				SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "select * from jiatiao where xs_id=? order by tj_date desc";
				PreparedStatement pstmt = null;
				ResultSet rst = null;
				ArrayList list = new ArrayList();
				try
					{
						pstmt = conn.prepareStatement(sql);
						pstmt.setInt(1, xs_id);
						rst = pstmt.executeQuery();
						while (rst.next())
							{
								JiaTiao jiaTiao = new JiaTiao();
								jiaTiao.setJt_id(rst.getInt("jt_id"));
								jiaTiao.setTj_date(rst.getDate("tj_date"));
								jiaTiao.setB_date(rst.getDate("b_date"));
								jiaTiao.setE_date(rst.getDate("e_date"));
								jiaTiao.setDays(rst.getInt("days"));
								jiaTiao.setLevel(rst.getInt("level"));
								jiaTiao.setReason(rst.getString("reason"));
								jiaTiao.setStatus_in1(rst.getInt("status_in1"));
								jiaTiao.setStatus_in2(rst.getInt("status_in2"));
								jiaTiao.setStatus_in3(rst.getInt("status_in3"));
								jiaTiao.setStatus_out1(rst
										.getInt("status_out1"));
								jiaTiao.setStatus_out2(rst
										.getInt("status_out2"));
								jiaTiao.setStatus_out3(rst
										.getInt("status_out3"));
								jiaTiao.setXs_id(rst.getInt("xs_id"));
								list.add(jiaTiao);
							}
					}
				catch (SQLException e)
					{
						e.printStackTrace();
					}
				finally
					{
						rst.close();
						pstmt.close();
						conn.close();
					}
				return list;

			}

		// ��ݰ���������״̬��ѯ������Ϣ
		public ArrayList findJiaTiaoByBan(String para, int b_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				switch (para)
					{
					case "ds":
						sql = "select * from jiatiao where status_out1=0 and xs_id in (select xs_id from ac01 where b_id=?) order by tj_date desc";
						break;
					case "dx":
						sql = "select * from jiatiao where ((level=1 and status_out1=1 and status_in1=0) or (level=2 and status_out1=1 and status_out2=1 and status_in1=0) or (level=3 and status_out1=1 and status_out2=1 and status_out3=1 and status_in1=0)) and xs_id in (select xs_id from ac01 where b_id=?) order by tj_date desc";
						break;
					case "ys":
						sql = "select * from jiatiao where status_out1=1 and xs_id in (select xs_id from ac01 where b_id=?) order by tj_date desc";
						break;
					case "yx":
						sql = "select * from jiatiao where status_in1=1 and xs_id in (select xs_id from ac01 where b_id=?) order by tj_date desc";
						break;

					}

				PreparedStatement pstmt = null;
				ResultSet rst = null;
				ArrayList list = new ArrayList();
				try
					{
						pstmt = conn.prepareStatement(sql);
						pstmt.setInt(1, b_id);
						rst = pstmt.executeQuery();
						while (rst.next())
							{
								JiaTiao jiaTiao = new JiaTiao();
								jiaTiao.setJt_id(rst.getInt("jt_id"));
								jiaTiao.setTj_date(rst.getDate("tj_date"));
								jiaTiao.setB_date(rst.getDate("b_date"));
								jiaTiao.setE_date(rst.getDate("e_date"));
								jiaTiao.setDays(rst.getInt("days"));
								jiaTiao.setLevel(rst.getInt("level"));
								jiaTiao.setReason(rst.getString("reason"));
								jiaTiao.setStatus_in1(rst.getInt("status_in1"));
								jiaTiao.setStatus_in2(rst.getInt("status_in2"));
								jiaTiao.setStatus_in3(rst.getInt("status_in3"));
								jiaTiao.setStatus_out1(rst
										.getInt("status_out1"));
								jiaTiao.setStatus_out2(rst
										.getInt("status_out2"));
								jiaTiao.setStatus_out3(rst
										.getInt("status_out3"));
								jiaTiao.setXs_id(rst.getInt("xs_id"));
								list.add(jiaTiao);
							}
					}
				catch (SQLException e)
					{
						e.printStackTrace();
					}
				finally
					{
						rst.close();
						pstmt.close();
						conn.close();
					}
				return list;

			}

		// ��ݰ��������״̬��ѯ������Ϣ
		public ArrayList findJiaTiaoByStatus_in1(int status_in1, int b_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "select * from jiatiao where status_in1=? and xs_id in (select xs_id from ac01 where b_id=?) order by tj_date desc";
				PreparedStatement pstmt = null;
				ResultSet rst = null;
				ArrayList list = new ArrayList();
				try
					{
						pstmt = conn.prepareStatement(sql);
						pstmt.setInt(1, status_in1);
						pstmt.setInt(2, b_id);
						rst = pstmt.executeQuery();
						while (rst.next())
							{
								JiaTiao jiaTiao = new JiaTiao();
								jiaTiao.setJt_id(rst.getInt("jt_id"));
								jiaTiao.setTj_date(rst.getDate("tj_date"));
								jiaTiao.setB_date(rst.getDate("b_date"));
								jiaTiao.setE_date(rst.getDate("e_date"));
								jiaTiao.setDays(rst.getInt("days"));
								jiaTiao.setLevel(rst.getInt("level"));
								jiaTiao.setReason(rst.getString("reason"));
								jiaTiao.setStatus_in1(rst.getInt("status_in1"));
								jiaTiao.setStatus_in2(rst.getInt("status_in2"));
								jiaTiao.setStatus_in3(rst.getInt("status_in3"));
								jiaTiao.setStatus_out1(rst
										.getInt("status_out1"));
								jiaTiao.setStatus_out2(rst
										.getInt("status_out2"));
								jiaTiao.setStatus_out3(rst
										.getInt("status_out3"));
								jiaTiao.setXs_id(rst.getInt("xs_id"));
								list.add(jiaTiao);
							}
					}
				catch (SQLException e)
					{
						e.printStackTrace();
					}
				finally
					{
						try
							{
								rst.close();
								pstmt.close();
								conn.close();
							}
						catch (SQLException e)
							{
								e.printStackTrace();
							}
					}
				return list;

			}

		// ���������������
		public int updateStatus_out1(String action, int jt_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				int success = 0;
				switch (action)
					{
					case "yx":
						sql = "update jiatiao set status_out1=1 where jt_id=?";
						break;
					case "jj":
						sql = "update jiatiao set status_out1=2 where jt_id=?";
					}
				PreparedStatement pstmt = null;
				ResultSet rst = null;

				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, jt_id);
				success = pstmt.executeUpdate();

				return success;

			}

		// ���������������
		public int updateStatus_in1(String action, int jt_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				int success = 0;
				switch (action)
					{
					case "yx":
						sql = "update jiatiao set status_in1=1 where jt_id=?";
						break;
					case "jj":
						sql = "update jiatiao set status_in1=2 where jt_id=?";
					}
				PreparedStatement pstmt = null;
				ResultSet rst = null;

				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, jt_id);
				success = pstmt.executeUpdate();

				return success;

			}

		// ���ϵ�쵼����״̬��ѯ������Ϣ
		public ArrayList findJiaTiaoByXi(String para, int x_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				switch (para)
					{
					case "ds":
						sql = "select * from jiatiao where (status_out2=0 and level<>1 and status_out1=1 ) and xs_id in (select xs_id from ac01 where b_id in (select b_id from ac02 where x_id=?)) order by tj_date desc";
						break;
					case "dx":
						sql = "select * from jiatiao where (status_in2=0 and ((level=2 and status_out1=1 and status_out2=1 and status_in1=1) or (level=3 and status_out1=1 and status_out2=1 and status_out3=1 and status_in1=1))) and xs_id in (select xs_id from ac01 where b_id in (select b_id from ac02 where x_id=?)) order by tj_date desc";
						break;
					case "ys":
						sql = "select * from jiatiao where status_out2=1 and xs_id in (select xs_id from ac01 where b_id in (select b_id from ac02 where x_id=?)) order by tj_date desc";
						break;
					case "yx":
						sql = "select * from jiatiao where status_in2=1 and xs_id in (select xs_id from ac01 where b_id in (select b_id from ac02 where x_id=?)) order by tj_date desc";
						break;

					}

				PreparedStatement pstmt = null;
				ResultSet rst = null;
				ArrayList list = new ArrayList();
				try
					{
						pstmt = conn.prepareStatement(sql);
						pstmt.setInt(1, x_id);
						rst = pstmt.executeQuery();
						while (rst.next())
							{
								JiaTiao jiaTiao = new JiaTiao();
								jiaTiao.setJt_id(rst.getInt("jt_id"));
								jiaTiao.setTj_date(rst.getDate("tj_date"));
								jiaTiao.setB_date(rst.getDate("b_date"));
								jiaTiao.setE_date(rst.getDate("e_date"));
								jiaTiao.setDays(rst.getInt("days"));
								jiaTiao.setLevel(rst.getInt("level"));
								jiaTiao.setReason(rst.getString("reason"));
								jiaTiao.setStatus_in1(rst.getInt("status_in1"));
								jiaTiao.setStatus_in2(rst.getInt("status_in2"));
								jiaTiao.setStatus_in3(rst.getInt("status_in3"));
								jiaTiao.setStatus_out1(rst
										.getInt("status_out1"));
								jiaTiao.setStatus_out2(rst
										.getInt("status_out2"));
								jiaTiao.setStatus_out3(rst
										.getInt("status_out3"));
								jiaTiao.setXs_id(rst.getInt("xs_id"));
								list.add(jiaTiao);
							}
					}
				catch (SQLException e)
					{
						e.printStackTrace();
					}
				finally
					{
						rst.close();
						pstmt.close();
						conn.close();
					}
				return list;

			}

		// ϵ�쵼���������
		public int updateStatus_out2(String action, int jt_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				int success = 0;
				switch (action)
					{
					case "yx":
						sql = "update jiatiao set status_out2=1 where jt_id=?";
						break;
					case "jj":
						sql = "update jiatiao set status_out2=2 where jt_id=?";
					}
				PreparedStatement pstmt = null;
				ResultSet rst = null;

				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, jt_id);
				success = pstmt.executeUpdate();

				return success;

			}

		// ϵ�쵼���������
		public int updateStatus_in2(String action, int jt_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				int success = 0;
				switch (action)
					{
					case "yx":
						sql = "update jiatiao set status_in2=1 where jt_id=?";
						break;
					case "jj":
						sql = "update jiatiao set status_in2=2 where jt_id=?";
					}
				PreparedStatement pstmt = null;
				ResultSet rst = null;

				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, jt_id);
				success = pstmt.executeUpdate();

				return success;

			}

		// ���Ժ�쵼����״̬��ѯ������Ϣ
		public ArrayList findJiaTiaoByYuan(String para, int y_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				switch (para)
					{
					case "ds":
						sql = "select * from jiatiao where (status_out3=0 and level=3 and status_out1=1 and status_out2=1) and xs_id in (select xs_id from ac01 where b_id in (select b_id from ac02 where x_id in (select x_id from ac03 where y_id=?))) order by tj_date desc";
						break;
					case "dx":
						sql = "select * from jiatiao where status_in3=0 and level=3 and status_out1=1 and status_out2=1 and status_out3=1 and status_in1=1 and status_in2=1 and xs_id in (select xs_id from ac01 where b_id in (select b_id from ac02 where x_id in (select x_id from ac03 where y_id=?))) order by tj_date desc";
						break;
					case "ys":
						sql = "select * from jiatiao where status_out3=1 and xs_id in (select xs_id from ac01 where b_id in (select b_id from ac02 where x_id in (select x_id from ac03 where y_id=?))) order by tj_date desc";
						break;
					case "yx":
						sql = "select * from jiatiao where status_in3=1 and xs_id in (select xs_id from ac01 where b_id in (select b_id from ac02 where x_id in (select x_id from ac03 where y_id=?))) order by tj_date desc";
						break;

					}

				PreparedStatement pstmt = null;
				ResultSet rst = null;
				ArrayList list = new ArrayList();
				try
					{
						pstmt = conn.prepareStatement(sql);
						pstmt.setInt(1, y_id);
						rst = pstmt.executeQuery();
						while (rst.next())
							{
								JiaTiao jiaTiao = new JiaTiao();
								jiaTiao.setJt_id(rst.getInt("jt_id"));
								jiaTiao.setTj_date(rst.getDate("tj_date"));
								jiaTiao.setB_date(rst.getDate("b_date"));
								jiaTiao.setE_date(rst.getDate("e_date"));
								jiaTiao.setDays(rst.getInt("days"));
								jiaTiao.setLevel(rst.getInt("level"));
								jiaTiao.setReason(rst.getString("reason"));
								jiaTiao.setStatus_in1(rst.getInt("status_in1"));
								jiaTiao.setStatus_in2(rst.getInt("status_in2"));
								jiaTiao.setStatus_in3(rst.getInt("status_in3"));
								jiaTiao.setStatus_out1(rst
										.getInt("status_out1"));
								jiaTiao.setStatus_out2(rst
										.getInt("status_out2"));
								jiaTiao.setStatus_out3(rst
										.getInt("status_out3"));
								jiaTiao.setXs_id(rst.getInt("xs_id"));
								list.add(jiaTiao);
							}
					}
				catch (SQLException e)
					{
						e.printStackTrace();
					}
				finally
					{
						rst.close();
						pstmt.close();
						conn.close();
					}
				return list;

			}

		// Ժ�쵼���������
		public int updateStatus_out3(String action, int jt_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				int success = 0;
				switch (action)
					{
					case "yx":
						sql = "update jiatiao set status_out3=1 where jt_id=?";
						break;
					case "jj":
						sql = "update jiatiao set status_out3=2 where jt_id=?";
					}
				PreparedStatement pstmt = null;
				ResultSet rst = null;

				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, jt_id);
				success = pstmt.executeUpdate();

				return success;

			}

		// Ժ�쵼���������
		public int updateStatus_in3(String action, int jt_id)
				throws NamingException, SQLException
			{
				Connection conn = dbHelper.getConnection();
				String sql = "";
				int success = 0;
				switch (action)
					{
					case "yx":
						sql = "update jiatiao set status_in3=1 where jt_id=?";
						break;
					case "jj":
						sql = "update jiatiao set status_in3=2 where jt_id=?";
					}
				PreparedStatement pstmt = null;
				ResultSet rst = null;

				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, jt_id);
				success = pstmt.executeUpdate();

				return success;

			}

	}
